package com.my.easypoi.test.simple;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.my.easypoi.utils.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.util.LinkedHashMap;

/*excel读取为json 旌阳区*/
@Slf4j
public class Excel4 {
//    private final static Logger logger = LoggerFactory.getLogger(ExcelTranslator.class);

    private final static ObjectMapper mapper = new ObjectMapper();
    /*读取excel的值为json*/
    public static void main(String[] args) {
        try {
            //最终返回的json对象
            JSONObject rowObj = new JSONObject(new LinkedHashMap());

            FileInputStream inp = new FileInputStream("D:\\tmp\\0514point-2.xlsx");
            Workbook workbook = WorkbookFactory.create(inp);
            Sheet sheettimo = workbook.getSheetAt(0);//要读取页
            int rownum11s= sheettimo.getPhysicalNumberOfRows();//总行数

            JSONArray jsonArray = new JSONArray();//array类型 子集存放位置

            for(int a = 3; a < rownum11s ; a++){
                JSONObject sunrowObj = new JSONObject(new LinkedHashMap());//array类型中的每一个json对象存放
                String cover_num = getMergedRegionValue(sheettimo, a, 4);
                sunrowObj.put("cover_num",cover_num);
                String mapAddr = getMergedRegionValue(sheettimo, a, 7);
                if(mapAddr!=null){
                    boolean de = mapAddr.startsWith("德阳市");
                    if(!de){
                        mapAddr ="德阳市"+mapAddr;
                    }
                }
                sunrowObj.put("mapAddr",mapAddr);
                String name = getMergedRegionValue(sheettimo, a, 5);
                sunrowObj.put("name",name);
                String address = getMergedRegionValue(sheettimo, a, 6);
                sunrowObj.put("address",address);

                String person_point_org_organizer = getMergedRegionValue(sheettimo, a, 9);
                sunrowObj.put("person_point_org_organizer",person_point_org_organizer);
                String person_point_org_organizer_phone = getMergedRegionValue(sheettimo, a, 10);
                sunrowObj.put("person_point_org_organizer_phone",person_point_org_organizer_phone);

                String person_point_skill_organizer = getMergedRegionValue(sheettimo, a, 11);
                sunrowObj.put("person_point_skill_organizer",person_point_skill_organizer);
                String person_point_skill_organizer_phone = getMergedRegionValue(sheettimo, a, 12);
                sunrowObj.put("person_point_skill_organizer_phone",person_point_skill_organizer_phone);

                String person_point_cy = getMergedRegionValue(sheettimo, a, 13);
                sunrowObj.put("person_point_cy",person_point_cy);
                String person_point_cy_phone = getMergedRegionValue(sheettimo, a, 14);
                sunrowObj.put("person_point_cy_phone",person_point_cy_phone);


                String person_point_zy = getMergedRegionValue(sheettimo, a, 17);
                sunrowObj.put("person_point_zy",person_point_zy);
                String person_point_zy_phone = getMergedRegionValue(sheettimo, a, 18);
                sunrowObj.put("person_point_zy_phone",person_point_zy_phone);
                String person_point_zy_car = getMergedRegionValue(sheettimo, a, 19);
                sunrowObj.put("person_point_zy_car",person_point_zy_car);

                String ylws_org = getMergedRegionValue(sheettimo, a, 20);
                sunrowObj.put("ylws_org",ylws_org);

                String org_id = getMergedRegionValue(sheettimo, a, 22);
                sunrowObj.put("org_id",org_id);

                String cy_worker_num = getMergedRegionValue(sheettimo, a, 23);
                sunrowObj.put("cy_worker_num",cy_worker_num);

                jsonArray.add(sunrowObj);
                log.info("====("+a+")==========");

            }
            log.info(jsonArray.toJSONString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String getMergedRegionValue(Sheet sheettimo, int row, int column){
        Row row1 = sheettimo.getRow(row);//获取第a行
        if(row1==null){
            return null;
        }
        boolean mergedRegion = ExcelUtils.isMergedRegion(sheettimo, row, column);
        String mergedRegionValue=null;
        if(mergedRegion){
            mergedRegionValue = ExcelUtils.getMergedRegionValue(sheettimo, row, column);
        }else{
            Cell cellData = row1.getCell(column);
            if(cellData==null){
                return null;
            }
//            try {
//                mergedRegionValue = cellData.getStringCellValue();
//            }catch (Exception e){
//                mergedRegionValue = "xxx";
//            }
            mergedRegionValue = ExcelUtils.getCellValue(cellData);
        }
        return mergedRegionValue;
    }
}